In this article, we will cover working with report templates using the Remote API. Executing reports through the SDK implies a sequence of mandatory actions that are not visible when working in the web interface. To complete the picture, examples of working with reports with and without grouping will be given.

Creating Reports

In most cases, users create report templates in the web interface and then execute them using API requests. We will be considering such cases later in this article.

However, you can create a report template through the SDK by using the request report/update_report.

Sequence of Actions

To obtain report results using the Remote API, several consecutive requests need to be sent. The use of some of them depends on the presence of grouping in the report and the amount of analyzed data. Here is the general list of actions:

1. Authorization through the request token/login and setting localization through the request render/set_locale.
   
   

2. Obtaining the necessary system IDs for:
   

   • the object the report needs to be executed for;

     Since reports can be executed for drivers, trailers, passengers, geozones, and their groups, in some cases it will also be necessary to obtain system IDs for these subitems.

   • the resource that contains the report template;
     
   • the report template itself.
     
     

3. Executing the report through the request report/exec_report.

   In general, we recommend executing the report in the background mode on the server. Despite the increase in the number of steps, this can be useful if the report takes a long time to execute due to a large number of objects, report execution interval, number of tables and charts, etc. Later in this article, we will specifically consider executing the report in the background mode.
   
   If the report is not executed in the background mode on the server, then the response to this request will be similar to the response from step 5, so you can proceed directly to step 6.
   

4. Checking the report execution status through the request report/get_report_status.
   After confirming the report is ready, you can proceed with following the instructions.
   
   
5. Obtaining the report result through the request report/apply_report_result.
   The result of this request will provide the number of rows, columns, and levels of nesting if grouping is used.
   
   
6. Getting the table rows through the request report/get_result_rows for a report without grouping or through the request report/select_result_rows for a report with grouping.
   The selection of the table, nesting levels, and displayed rows is based on the data from the previous response.
   
   
7. Exporting to a file through the request report/export_result.
   This step is optional but quite simple and useful, so we will also consider it.
   
   
8. Deleting the result of the previous report through the request report/cleanup_result.
   This step is mandatory if multiple reports must be executed in one session.

Next, we will consider two examples of working with reports (without grouping and with grouping).

Working with a Report without Grouping

Let’s imagine, it we need to obtain the results of the the report List of trips, available to the user authorized by a token, for the unit Truck 0769 (system ID 55555) for the time interval from 2023 June 30 20:00 to 2023 July 01 03:59 (GMT+0) and export it to a PDF file using API requests.

In the web interface, the result looks as follows:

1. Authorization and Localization

Use the request token/login.

https://hst-api.wialon.com/wialon/ajax.html?svc=token/login&params={"token":"TOKEN_VALUE"}

Authorization details are described in one of the previous articles.

Localization settings include setting the time zone (which has also been discussed earlier), date format, and other parameters.

Use the request render/set_locale.

https://hst-api.wialon.com/wialon/ajax.html?svc=render/set_locale&params={"tzOffset":134217728,"language":"en","formatDate":"%25E.%25m.%25Y%20%25H:%25M:%25S"}&sid=SESSION_IDENTIFIER

2. Obtaining System IDs

In one of the previous articles, we have already described how to search items by property, so now we will focus only on searching a resource and report template.

Let’s get a list of all resources that have a report template named List of trips available to the user authorized by a token. Use the request core/search_items:

https://hst-api.wialon.com/wialon/ajax.html?svc=core/search_items&params={"spec":{"itemsType":"avl_resource","propType":"propitemname","propName":"reporttemplates","propValueMask":"List%20of%20trips","sortType":"sys_name"},"force":1,"flags":8193,"from":0,"to":0}&sid=SESSION_IDENTIFIER

{
	"searchSpec":{
		"itemsType":"avl_resource",
		"propName":"reporttemplates",
		"propValueMask":"List of trips",
		"sortType":"sys_name",
		propType:"propitemname",
		or_logic:"0"
		},
	"dataFlags":8193,
	"totalItemsCount":1,
	"indexFrom":0,
	"indexTo":0,
	"items":[
		{
			"nm":"sdk_account",
			"cls":3,
			"id":12345678,
			"mu":0,
			"rep":{
				1:{
					"id":1,
					"n":"List of trips",
					"ct":"avl_unit",
					"c":59352
				},
				2:{
					"id":2,
					"n":"Grouped trips",
					"ct":"avl_unit",
					"c":6883
				}
			},
			"repmax":-1,
			"uacl":-1
		}
	]
}

Pay attention to the following parameters in the response:

• nm — resource name;

• rep — an array of report templates created in the resource;

  If this parameter is displayed as empty, it means that there are no report templates created in the resource.

• n — subitem name;
• id — system ID.

In this case, the user has access to the sdk_account resource with system ID 12345678, which contains the report template List of trips with system ID 1.

3. Executing the Report

Use the request report/exec_report.

https://hst-api.wialon.com/wialon/ajax.html?svc=report/exec_report&params={"reportResourceId":12345678,"reportTemplateId":1,"reportObjectId":55555,"reportObjectSecId":0,"interval":{"flags":0,"from":1688144400,"to":1688173199},"remoteExec":1}&sid=SESSION_IDENTIFIER

{
	"remoteExec":1
}

4. Checking the Execution Status

Since the report is being executed in the background on the server, we will check its execution status using the request report/get_report_status.

https://hst-api.wialon.com/wialon/ajax.html?svc=report/get_report_status&params={}&sid=SESSION_IDENTIFIER

{
	"status":4
}

A status code 4 means that the report has been executed successfully. The interpretation of other values can be found on the page with a request description.

5. Obtaining the Report Result

Use the request report/apply_report_result.

https://hst-api.wialon.com/wialon/ajax.html?svc=report/apply_report_result&params={}&sid=SESSION_IDENTIFIER

{
	"reportResult":{
		"msgsRendered":0,
		"stats":[],
		"tables":[
			{
				"name":"unit_trips",
				"label":"Trips",
				"grouping":{},
				"flags":4224,
				"rows":4,
				"level":1,
				"columns":5,
				"header":[
					"№",
					"Beginning",
					"End",
					"Duration",
					"Mileage"
				],
				"header_type":[
					"",
					"time_begin",
					"time_end",
					"duration",
					"mileage"
				]
			}
		],
		"attachments":[]
	}
}

Three key parameters from the response that are important for us here are as follows:

• tables — shows the number of tables in the report as an array. In this case, only one table (Trips) is mentioned, therefore its index will be 0.
• rows — the number of rows in the table, which is 4 in this case. Therefore, their indexes range from 0 to 3.
• level — the number of levels of nesting, which is 1 in this case, as there is no grouping in the report.

Other parameters from the response can also be briefly listed. The columns parameter indicates the number of columns, and their names are described in the header parameter. A value of 0 for the msgsRendered parameter indicates that the report template doesn't display tracks on the map. Empty stats and attachments parameters indicate that there are no Statistics nor attachments (e.g., charts) in the report template.

6. Getting Table Rows

Knowing the index of the table content, let's display it using the request report/get_result_rows.

https://hst-api.wialon.com/wialon/ajax.html?svc=report/get_result_rows&params={"tableIndex":0,"indexFrom":0,"indexTo":3}&sid=SESSION_IDENTIFIER

[
	{
		"n":0,
		"i1":0,
		"i2":790,
		"t1":1688144420,
		"t2":1688154878,
		"d":0,
		"c":[
			"1",
			{
				"t":"2023-06-30 20:00:20",
				"v":1688144420,
				"y":47.2941741943,
				"x":26.4906959534,
				"u":55555
			},
			{
				"t":"2023-06-30 22:54:38",
				"v":1688154878,
				"y":43.8697662354,
				"x":26.0177116394,
				"u":55555
			},
			"2:54:18",
			"469.54 km"
		]
	},
	{
		"n":1,
		"i1":936,
		"i2":2171,
		"t1":1688155181,
		"t2":1688169690,
		"d":0,
		"c":[
			"2",
			{
				"t":"2023-06-30 22:59:41",
				"v":1688155181,
				"y":43.8698196411,
				"x":26.0177154541,
				"u":55555
			},
			{
				"t":"2023-07-01 03:01:30",
				"v":1688169690,
				"y":41.7139854431,
				"x":26.3660545349,
				"u":55555
			},
			"4:01:49",
			"343.84 km"
		]
	},
	{
		"n":2,
		"i1":2340,
		"i2":2486,
		"t1":1688170034,
		"t2":1688170841,
		"d":0,
		"c":[
			"3",
			{
				"t":"2023-07-01 03:07:14",
				"v":1688170034,
				"y":41.7140579224,
				"x":26.365901947,
				"u":55555
			},
			{
				"t":"2023-05-01 09:23:10",
				"v":1688170841,
				"y":41.7122383118,
				"x":26.3712425232,
				"u":55555
			},
			"0:13:27",
			"1.39 km"
		]
	},
	{
		"n":3,
		"i1":2833,
		"i2":2910,
		"t1":1688171565,
		"t2":1688173175,
		"d":0,
		"c":[
			"4",
			{
				"t":"2023-07-01 03:32:45",
				"v":1688171565,
				"y":41.7120819092,
				"x":26.3711204529,
				"u":55555
			},
			{
				"t":"2023-07-01 03:59:35",
				"v":1688173175,
				"y":41.5760040283,
				"x":26.9871864319,
				"u":55555
			},
			"0:26:50",
			"57.84 km"
		]
	}
]

7. Export to File

Let’s export the result to a PDF file using the request report/export_result.

https://hst-api.wialon.com/wialon/ajax.html?svc=report/export_result&params={"format":2,"compress":0,"outputFileName":"List%20of%20trips"}&sid=SESSION_IDENTIFIER

There is no response displayed for this API request, instead, the file download starts automatically.

8. Deleting the Result of the Previous Report

Use the request report/cleanup_result.

https://hst-api.wialon.com/wialon/ajax.html?svc=report/cleanup_result&params={}&sid=SESSION_IDENTIFIER

{
	"error":0
}

A zero value indicates successful deletion.

Working with a Report with Grouping

Now we need to obtain the results of the report Grouped trips, available to the user authorized by a token, for the unit Truck 0769 (system ID 55555) for the time interval from 2023 June 30 20:00 to 2023 July 01 03:59 (GMT+0) and export it to a PDF file using API requests.

In the web interface, the result looks as follows:

Based on the result in the web interface, you can see that the Trips table has grouping by months and dates, meaning the report has 3 levels of nesting:

• The first level contains rows with months.
  The second level contains rows with dates within the month.
• The third level contains rows with trips within the date.

1. Authorization and Localization

Use the request token/login.

https://hst-api.wialon.com/wialon/ajax.html?svc=token/login&params={"token":"TOKEN_VALUE"}

Authorization details are described in one of the previous articles.

Localization settings include setting the time zone (which has also been discussed earlier), date format, and other parameters.

Use the request render/set_locale.

https://hst-api.wialon.com/wialon/ajax.html?svc=render/set_locale&params={"tzOffset":134217728,"language":"en","formatDate":"%25E.%25m.%25Y%20%25H:%25M:%25S"}&sid=SESSION_IDENTIFIER

2. Obtaining System IDs

In one of the previous articles, we have already described how to search items by property, so now we will focus only on searching a resource and report template.

Let’s get a list of all resources that have a report template named Grouped trips available to the user authorized by a token. Use the request core/search_items:

https://hst-api.wialon.com/wialon/ajax.html?svc=core/search_items&params={"spec":{"itemsType":"avl_resource","propType":"propitemname","propName":"reporttemplates","propValueMask":"Grouped%20trips","sortType":"sys_name"},"force":1,"flags":8193,"from":0,"to":0}&sid=SESSION_IDENTIFIER

{
	"searchSpec":{
		"itemsType":"avl_resource",
		"propName":"reporttemplates",
		"propValueMask":"Grouped trips",
		"sortType":"sys_name",
		propType:"propitemname",
		or_logic:"0"
		},
	"dataFlags":8193,
	"totalItemsCount":1,
	"indexFrom":0,
	"indexTo":0,
	"items":[
		{
			"nm":"sdk_account",
			"cls":3,
			"id":12345678,
			"mu":0,
			"rep":{
				1:{
					"id":1,
					"n":"List of trips",
					"ct":"avl_unit",
					"c":59352
				},
				2:{
					"id":2,
					"n":"Grouped trips",
					"ct":"avl_unit",
					"c":6883
				}
			},
			"repmax":-1,
			"uacl":-1
		}
	]
}

Pay attention to the following parameters from the response:

• nm — resource name;

• rep — an array of report templates created in the resource;

  If this parameter is displayed as empty, it means that no report templates are created in the resource.

• n — subitem name;
• id — system ID.

In this case, the user has access to the sdk_account resource with system ID 12345678, which contains the report template Grouped trips with system ID 2.

3. Executing the Report

Use the request report/exec_report.

https://hst-api.wialon.com/wialon/ajax.html?svc=report/exec_report&params={"reportResourceId":12345678,"reportTemplateId":2,"reportObjectId":55555,"reportObjectSecId":0,"interval":{"flags":0,"from":1688144400,"to":1688173199},"remoteExec":1}&sid=SESSION_IDENTIFIER

{
	"remoteExec":1
}

4. Checking the Execution Status

Since the report is being executed in the background on the server, let’s check the status of its execution using the request report/get_report_status.

https://hst-api.wialon.com/wialon/ajax.html?svc=report/get_report_status&params={}&sid=SESSION_IDENTIFIER

{
	"status":4
}

Status code 4 indicates that the report has been executed successfully. The interpretation of other values can be found on the page with a request description.

5. Obtaining the Report Result

Use the request report/apply_report_result.

https://hst-api.wialon.com/wialon/ajax.html?svc=report/apply_report_result&params={}&sid=SESSION_IDENTIFIER

{
	"reportResult":{
		"msgsRendered":0,
		"stats":[],
		"tables":[
			{
				"name":"unit_trips",
				"label":"Trips",
				"grouping":{
					"nested":{
						"type":"day"
					},
					"type":"month"
				},
				"flags":4491,
				"rows":2,
				"level":3,
				"columns":6,
				"header":[
					"№",
					"Grouping",
					"Beginning",
					"End",
					"Duration",
					"Mileage"
				],
				"header_type":[
					"",
					"",
					"time_begin",
					"time_end",
					"duration",
					"mileage"
				]
			}
		],
		"attachments":[]
	}
}

Three key parameters from the response that are important for us here are as follows:

• tables — shows the number of tables in the report as an array. In this case, only one table (Trips) is mentioned, therefore its index will be 0.
• rows — the number of rows in the table, which is 2 in this case. Therefore, their indexes range from 0 to 1. This refers only to the rows at the top level of nesting, there may be more inside.
• level — the number of levels of nesting, which is 3 in this case, as there is grouping in the report. Therefore, there are levels with indexes from 0 to 2 in the table.

Other parameters from the response can also be briefly listed. The columns parameter indicates the number of columns, and their names are described in the header parameter. A value of 0 for the msgsRendered parameter indicates that the report template doesn't display tracks on the map. Empty stats and attachments parameters indicate that there are no Statistics nor attachments (e.g., charts) in the report template.

6. Selecting Rows in a Multi-Level Table

Knowing the index of the table content, let’s display it using the request report/select_result_rows.

https://hst-api.wialon.com/wialon/ajax.html?svc=report/select_result_rows&params={"tableIndex":0,"config":{"type":"range","data":{"from":0,"to":1,"level":2}}}&sid=SESSION_IDENTIFIER

[
	{
		"n":0,
		"i1":0,
		"i2":1188,
		"t1":1688144420,
		"t2":1688158739,
		"d":1,
		"c":[
			"1",
			"June",
			{
				"t":"20:00:20",
				"v":1688144420,
				"y":47.2941741943,
				"x":26.4906959534,
				"u":55555
			},
			{
				"t":"23:58:59",
				"v":1688158739,
				"y":43.1049079895,
				"x":25.6173667908,
				"u":55555
			},
			"3:53:36",
			"576.60 km"
		],
		"r":[
			{
				"n":0,
				"i1":0,
				"i2":1188,
				"t1":1688144420,
				"t2":1688158739,
				"d":2,
				"c":[
					"1.1",
					"2023-06-30",
					{
						"t":"20:00:20",
						"v":1688144420,
						"y":47.2941741943,
						"x":26.4906959534,
						"u":55555
					},
					{
						"t":"23:58:59",
						"v":1688158739,
						"y":43.1049079895,
						"x":25.6173667908,
						"u":55555
					},
					"3:53:36",
					"576.60 km"
				],
				"r":[
					{
						"n":0,
						"i1":0,
						"i2":790,
						"t1":1688144420,
						"t2":1688154878,
						"d":0,
						"c":[
							"1.1.1",
							"2023-06-30 20:00:20",
							{
								"t":"20:00:20",
								"v":1688144420,
								"y":47.2941741943,
								"x":26.4906959534,
								"u":55555
							},
							{
								"t":"22:54:38",
								"v":1688154878,
								"y":43.8697662354,
								"x":26.0177116394,
								"u":55555
							},
							"2:54:18",
							"469.54 km"
						]
					},
					{
						"n":1,
						"i1":936,
						"i2":1188,
						"t1":1688155181,
						"t2":1688158739,
						"d":0,
						"c":[
							"1.1.2",
							"2023-06-30 22:59:41",
							{
								"t":"22:59:41",
								"v":1688155181,
								"y":43.8698196411,
								"x":26.0177154541,
								"u":55555
							},
							{
								"t":"23:58:59",
								"v":1688158739,
								"y":43.1049079895,
								"x":25.6173667908,
								"u":55555
							},
							"0:59:18",
							"107.06 km"
						]
					}
				]
			}
		]
	},
	{
		"n":1,
		"i1":1193,
		"i2":2910,
		"t1":1688158805,
		"t2":1688173175,
		"d":1,
		"c":[
			"2",
			"July",
				{
					"t":"00:00:05",
					"v":1688158805,
					"y":43.0983314514,
					"x":25.6316585541,
					"u":55555
				},
				{
					"t":"03:59:35",
					"v":1688173175,
					"y":41.5760040283,
					"x":26.9871864319,
					"u":55555
				},
				"3:41:42",
				"294.55 km"
		],
		"r":[
			{
				"n":0,
				"i1":1193,
				"i2":2910,
				"t1":1688158805,
				"t2":1688173175,
				"d":3,
				"c":[
					"2.1",
					"2023-07-01",
					{
						"t":"00:00:05",
						"v":1688158805,
						"y":43.0983314514,
						"x":25.6316585541,
						"u":55555
					},
					{
						"t":"03:59:35",
						"v":1688173175,
						"y":41.5760040283,
						"x":26.9871864319,
						"u":55555
					},
					"3:41:42",
					"294.55 km"
				],
				"r":[
					{
						"n":0,
						"i1":1193,
						"i2":2171,
						"t1":1688158805,
						"t2":1688169690,
						"d":0,
						"c":[
							"2.1.1",
							"2023-07-01 00:00:05",
							{
								"t":"00:00:05",
								"v":1688158805,
								"y":43.0983314514,
								"x":25.6316585541,
								"u":55555
							},
							{
								"t":"03:01:30",
								"v":1688169690,
								"y":41.7139854431,
								"x":26.3660545349,
								"u":55555
							},
							"3:01:25",
							"235.31 km"
						]
					},
					{
						"n":1,
						"i1":2340,
						"i2":2486,
						"t1":1688170034,
						"t2":1688170841,
						"d":0,
						"c":[
							"2.1.2",
							"2023-07-01 03:07:14",
							{
								"t":"03:07:14",
								"v":1688170034,
								"y":41.7140579224,
								"x":26.365901947,
								"u":55555
							},
							{
								"t":"03:20:41",
								"v":1688170841,
								"y":41.7122383118,
								"x":26.3712425232,
								"u":55555
							},
							"0:13:27",
							"1.39 km"
						]
					},
					{
						"n":2,
						"i1":2833,
						"i2":2910,
						"t1":1688171565,
						"t2":1688173175,
						"d":0,
						"c":[
							"2.1.3",
							"2023-07-01 03:32:45",
							{
								"t":"03:32:45",
								"v":1688171565,
								"y":41.7120819092,
								"x":26.3711204529,
								"u":55555
							},
							{
								"t":"03:59:35",
								"v":1688173175,
								"y":41.5760040283,
								"x":26.9871864319,
								"u":55555
							},
							"0:26:50",
							"57.84 km"
						]
					}
				]
			}
		]
	}
]

7. Export to File

Let’s export the result to a PDF file using the request report/export_result.

https://hst-api.wialon.com/wialon/ajax.html?svc=report/export_result&params={"format":2,"compress":0,"outputFileName":"Grouped%20trips"}&sid=SESSION_IDENTIFIER

There is no response displayed for this API request, instead, the file download starts automatically.

8. Deleting the Result of the Previous Report

Use the request report/cleanup_result.

https://hst-api.wialon.com/wialon/ajax.html?svc=report/cleanup_result&params={}&sid=SESSION_IDENTIFIER

{
	"error":0
}

A zero value indicates successful deletion.

Peculiarities of Obtaining Results

Obtaining data from tables can be performed using the requests report/get_result_rows or report/select_result_rows, which use the parameter tableIndex to access a table with a specific index. At the same time, it is necessary to take into account that when executing a report for different intervals, the index of the same table may change due to the presence or absence of other tables.

To better understand the situation, let's consider an example. Let’s suppose the report template includes tables for Trips, Fuel Fillings, and Geofences. When executing the report for the interval from July 1st to 3rd, the result will contain all tables, and therefore their indexes will have the following values:

0 — Trips
1 — Fuel Drains
2 — Geofences

At the same time, when executing the report for the interval from July 4th to 6th, the indexes of some tables will change due to the absence of registered fuel fillings and will take other values:

0 — Trips
1 — Geofences

In this example, it is clear that when executing the report for different intervals, the index of the Geofences table changes. Therefore, accessing the table with index 2 will not always display information about visiting geofences. To fix such situations, it is recommended to apply additional checks, for example, based on the table name or the names of its columns.

Ekaterina Grib,Customer Service Engineer 2023-08-31